package service.impl;

import req.StudentRequest;
import res.TableDTO;
import service.StudentService;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class StudentServiceImpl implements StudentService {
    @Override
    public TableDTO retrieveSrudents(StudentRequest studentRequest) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from student ");
        if(studentRequest.getSearchKey()!=null&&"".equals(studentRequest.getSearchKey().trim())){
            sql.append(" where name like '%"+studentRequest.getSearchKey().trim()+"%'");
        }
        sql.append("order by id desc limit").append(studentRequest.getStart()).append(",")
                .append(studentRequest.getPageSize());
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        TableDTO returnDTO=new TableDTO();

        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            rs=ps.executeQuery();
            returnDTO.setData(fillData(rs));
            sql.setLength(0);
            sql.append("select count(*) from student");
            if(studentRequest.getSearchKey()!=null&&!"".equals(studentRequest.getSearchKey().trim())){
                sql.append(" where name like '%"+studentRequest.getSearchKey().trim()+"%'");
            }
            ps=conn.prepareStatement(sql.toString());
            rs=ps.executeQuery();
            while(rs.next()){
                int count=rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>>data=new Vector<>();
        while (rs.next()){
            Vector<Object>oneRecord=new Vector<>();
            int id= rs.getInt("id");
            String name= rs.getString("name");
            String gender= rs.getString("gender");
            String birthday= rs.getString("birthday");
            double chinese= rs.getDouble("chinese");
            double math= rs.getDouble("math");
            double english= rs.getDouble("english");
            double total=chinese+math+english;
            double average=total*1.0/3;
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(birthday);
            oneRecord.addElement(chinese);
            oneRecord.addElement(math);
            oneRecord.addElement(english);
            oneRecord.addElement(total);
            oneRecord.addElement(average);
            data.addElement(oneRecord);
        }
        return data;
    }
}
